package sk.pitonak.mhd;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.Collection;
import java.util.HashMap;
import java.util.Map;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class Database {
	private static final String DATABASE_NAME = "MHDDatabase";
	private static final int DATABASE_VERSION = 1;

	private static Map<Integer, String> sStops;
	private static String[] sStopsAsArray;
	
	private SQLiteDatabase mDB;

	public Database(Context context) {
		mDB = new DBHelper(context).getWritableDatabase();
	}

	public void close() {
		mDB.close();
	}

	public Map<Integer, String> getStops() {
		if(sStops == null) {
			sStops = new HashMap<Integer, String>();
			Cursor cursor = mDB.query(Stops.NAME, Stops.COLUMNS_QUERY, null, null, null, null, null);
			if(cursor != null) {
				try {
					if(cursor.moveToFirst()) do {
						sStops.put(cursor.getInt(0), cursor.getString(1));
					} while(cursor.moveToNext());
				}
				finally {
					cursor.close();
				}
			}
		}
		return sStops;
	}
	
	public String[] getStopsAsArray() {
		if(sStopsAsArray == null) {
			getStops();
			Collection<String> stops = sStops.values();
			sStopsAsArray = new String[stops.size()];
			stops.toArray(sStopsAsArray);
		}
		return sStopsAsArray;
	}

	public long getStopId(String name) {
		String selection = String.format(Stops.SELECTION_SEARCH, name);
		Cursor cursor = mDB.query(Stops.NAME, Stops.COLUMNS_SEARCH, selection, null, null, null, null);
		if(cursor != null) {
			try {
				if(cursor.moveToFirst()) {
					return cursor.getInt(0);
				}
				return -1;
			}
			finally {
				cursor.close();
			}
		}
		else {
			return -1;
		}
	}

	public void addToHistory(String fromString, String toString) {
		long from = getStopId(fromString);
		long to = getStopId(toString);
		
		if(from < 0 || to < 0) return;

		String selection = String.format(History.SELECTION_SEARCH, from, to);
		Cursor cursor = mDB.query(History.NAME, History.COLUMNS_SEARCH, selection, null, null, null, null);
		if(cursor != null) {
			if(!cursor.moveToFirst()) {
				ContentValues values = new ContentValues();
				values.put(History.KEY_LAST, System.currentTimeMillis());
				values.put(History.KEY_FROM, from);
				values.put(History.KEY_TO, to);
				mDB.insert(History.NAME, null, values);
			}
			cursor.close();
		}
	}

	public void removeFromHistory(long id) {
		mDB.delete(History.NAME, History.KEY_ID + " = " + id, null);
	}

	public boolean setFavoriteHistory(long id, boolean isChecked) {
		String selection = History.KEY_ID + " = " + id;
		ContentValues values = new ContentValues();
		values.put(History.KEY_FAVORITE, isChecked ? 1 : 0);
		Cursor cursor = mDB.query(History.NAME, History.COLUMNS_QUERY, selection, null, null, null, null);
		if(cursor != null) {
			if(cursor.moveToFirst()) {
				if(isChecked == (cursor.getInt(2) != 0)) {
					cursor.close();
					return false;
				}
			}
			cursor.close();
		}
		mDB.update(History.NAME, values, selection, null);
		return true;
	}

	public Cursor queryHistory() {
		return mDB.query(History.NAME, History.COLUMNS_QUERY, null, null, null, null, History.KEY_FAVORITE + " DESC, " + History.KEY_LAST + " DESC");
	}

	private interface Stops {
		public static final String KEY_ID = "_id";
		public static final String KEY_NAME = "name";
		public static final String KEY_LATITUDE = "lat";
		public static final String KEY_LONGITUDE = "lon";
	
		public static final String[] COLUMNS_SEARCH = new String[] { KEY_ID };
		public static final String SELECTION_SEARCH = String.format("%s LIKE '%s'", KEY_NAME, "%s");
		public static final String[] COLUMNS_QUERY = new String[] { KEY_ID, KEY_NAME };
	
		public static final String NAME = "`stops`";
		public static final String SQL_CREATE_TABLE =
			String.format("CREATE TABLE IF NOT EXISTS %s (%s INTEGER, %s STRING);", NAME, KEY_ID, KEY_NAME);
		public static final String DROP_TABLE = "DROP TABLE IF EXISTS " + NAME + ";";
	}

	private interface History {
		public static final String KEY_ID = "_id";
		public static final String KEY_FROM = "`from`";
		public static final String KEY_TO = "`to`";
		public static final String KEY_LAST = "last";
		public static final String KEY_FAVORITE = "favorite";
	
		public static final String[] COLUMNS_SEARCH = new String[] { KEY_ID };
		public static final String SELECTION_SEARCH = String
			.format("%s = %s AND %s = %s", KEY_FROM, "%d", KEY_TO, "%d");
		public static final String[] COLUMNS_QUERY = { KEY_FROM, KEY_TO, KEY_FAVORITE, KEY_ID };
	
		public static final String NAME = "history";
		public static final String SQL_CREATE_TABLE =
			String.format("CREATE TABLE IF NOT EXISTS %s (%s INTEGER PRIMARY KEY AUTOINCREMENT, %s INTEGER, %s INTEGER, %s INTEGER, %s INTEGER DEFAULT 0);", NAME,
				KEY_ID, KEY_FROM,
				KEY_TO, KEY_LAST, KEY_FAVORITE);
		public static final String DROP_TABLE = "DROP TABLE IF EXISTS " + NAME + ";";
	}

	private static class DBHelper extends SQLiteOpenHelper {
		private Context mContext;
	
		public DBHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
			mContext = context;
		}
	
		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL(Stops.SQL_CREATE_TABLE);
			readStops(db);
			db.execSQL(History.SQL_CREATE_TABLE);
		}
	
		private void readStops(SQLiteDatabase db) {
			InputStream in = null;
			BufferedReader reader = null;
			try {
				in = mContext.getAssets().open("stops.txt");
				reader = new BufferedReader(new InputStreamReader(in));
	
				String line;
				ContentValues values = new ContentValues();
				while((line = reader.readLine()) != null) {
					String[] split = line.split("#");
					int id = Integer.parseInt(split[0]);
					String name = split[1];
					values.put(Stops.KEY_ID, id);
					values.put(Stops.KEY_NAME, name);
					db.insert(Stops.NAME, null, values);
				}
			}
			catch(IOException e) {
				e.printStackTrace();
			}
			finally {
				try {
					reader.close();
					in.close();
				}
				catch(IOException e) {
					e.printStackTrace();
				}
			}
		}
	
		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			db.execSQL(Stops.DROP_TABLE);
			db.execSQL(History.DROP_TABLE);
			onCreate(db);
		}
	}
}
